package com.qjcc.util;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

/**
 * Description: 数据库连接池类
 * @filename DataSourceFactory.java 
 * @date 2013年8月21日 19:47:21
 * @author Herman.Xiong
 * @version 1.0
 * Copyright (c) 2013 Company,Inc. All Rights Reserved.
 */
public class SQLServerDataSourceFactoryCall {
    private static Logger log = Logger.getLogger(SQLServerDataSourceFactoryCall.class);
    private static BasicDataSource bs = null;


    public static String driver="net.sourceforge.jtds.jdbc.Driver";
    public static String url="jdbc:jtds:sqlserver://192.168.5.16/vsale";
    public static String  userName="qjzt_dba";
    public static String password="1234";

    private volatile static SQLServerDataSourceFactoryCall sqlServerDataSourceFactory;

    private SQLServerDataSourceFactoryCall(){};

    public static SQLServerDataSourceFactoryCall getInstance(){
        if(sqlServerDataSourceFactory == null){
            synchronized (SQLServerDataSourceFactoryCall.class) {
                if(sqlServerDataSourceFactory == null){
                    sqlServerDataSourceFactory = new SQLServerDataSourceFactoryCall();
                }
            }
        }

        return sqlServerDataSourceFactory;
    }
    
    boolean isDebug = false;

    /**
     * 根据类型查询需要同步的数据
     * */
    public List<Map<String , Object>>  getCallList()throws Exception{

    	List<Map<String,Object>> refList = null;

    	if(isDebug){
	    	/*******************************************************************************************************************/
	    	// 测试环境代码 begin
	    	java.io.ObjectInputStream ois = new java.io.ObjectInputStream(new java.io.FileInputStream("/Users/niejiao/custom/work/千嘉科技/呼叫中心/test.out"));
	    	refList = (List<Map<String, Object>>) ois.readObject();
	    	if(1 < 2)
	    		return refList;
	    	// 测试环境代码 end
	    	/*******************************************************************************************************************/
    	}


    	String sql = "select top 6 * from ( "+
                " SELECT CONVERT(varchar(7), i.call_date, 120) as call_date, COUNT(*) ct FROM ivr_dtmf i group by CONVERT(varchar(7), i.call_date, 120)) x " +
                " order by x.call_date desc ";

    	Connection con = SQLServerDataSourceFactoryCall.getConnection();
    	ResultSet rs = null;
    	PreparedStatement ps = null;
    	try{
    		
    		ps = con.prepareStatement(sql);
    		
    		rs = ps.executeQuery();
    		
    		// 构建返回数据
    		MapListHandler mlh = new MapListHandler();
    		
    		refList = mlh.handle(rs);

            System.out.println("返回数据size：" + refList.size());


        }finally{
    		SQLServerDataSourceFactoryCall.closeCon(rs, ps, con);
    	}
    	
    	
    	return refList;
    }

    public List<Map<String , Object>>  getCallListDay()throws Exception{

        List<Map<String,Object>> refList = null;

        String sql = "select i.call_date, DATENAME(DAY, i.call_date) as 'day', COUNT(*) as 'ct' from ivr_dtmf i where datediff(month,i.call_date,getdate())=0 group by i.call_date order by i.call_date";

        Connection con = SQLServerDataSourceFactoryCall.getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        try{

            ps = con.prepareStatement(sql);

            rs = ps.executeQuery();

            // 构建返回数据
            MapListHandler mlh = new MapListHandler();

            refList = mlh.handle(rs);

            System.out.println("返回数据size：" + refList.size());


        }finally{
            SQLServerDataSourceFactoryCall.closeCon(rs, ps, con);
        }


        return refList;
    }
    
    /**
     * 创建数据源
     * @return
     */
    public static BasicDataSource getDataSource() throws Exception{
        if(bs==null){
            log.info("数据库连接信息：[driver:"+driver+",url:"+url+",userName:"+userName+",password:"+password+"]");
            bs = new BasicDataSource();
            bs.setDriverClassName(driver);
            bs.setUrl(url);
            bs.setUsername(userName);
            bs.setPassword(password);
            bs.setMaxActive(200);//设置最大并发数
            bs.setInitialSize(5);//数据库初始化时，创建的连接个数
            bs.setMinIdle(5);//最小空闲连接数
            bs.setMaxIdle(200);//数据库最大连接数
            bs.setMaxWait(1000);
            bs.setMinEvictableIdleTimeMillis(60*1000);//空闲连接60秒中后释放
            bs.setTimeBetweenEvictionRunsMillis(5*60*1000);//5分钟检测一次是否有死掉的线程
            bs.setTestOnBorrow(true);
        }
        return bs;
    }
     
    /**
     * 释放数据源
     */
    public static void shutDownDataSource() throws Exception{
        if(bs!=null){
            bs.close();
        }
    }
     
    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection con=null;
        try {
            if(bs!=null){
                con=bs.getConnection();
            }else{
                con=getDataSource().getConnection();
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return con;
    }
     
    /**
     * 关闭连接
     */
    public static void closeCon(ResultSet rs,PreparedStatement ps,Connection con){
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                log.error("关闭结果集ResultSet异常！"+e.getMessage(), e);
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (Exception e) {
                log.error("预编译SQL语句对象PreparedStatement关闭异常！"+e.getMessage(), e);
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (Exception e) {
                log.error("关闭连接对象Connection异常！"+e.getMessage(), e);
            }
        }
    }
}